﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class TipController
    {
        private DBConnector connector;

        public TipController()
        {
            connector = new DBConnector();
        }

        public DataTable getData()
        {
            string query = "SELECT dateTip as 'Date', FORMAT(tipOftheDay,2) as 'Tip of the Day' FROM tip ORDER BY dateTip DESC";
            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                return dataTable;
            }
            else
            {
                return null;
            }
        }

        public bool addTip(Tip tip)
        {
            string query = "INSERT INTO tip(dateTip, tipOftheDay) VALUES (\'" + tip.getDate() + "\'," + tip.getTip() + ");";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("That date already exists. Input another date, or use edit to change the tip for that date.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
            }
            else
                return false;
        }

        public bool editTip(Tip tip,String date)
        {
            string query = "UPDATE tip set dateTip = \'"+tip.getDate()+"\', tipOftheDay = "+tip.getTip()+" where dateTip = \'"+date+"\';";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("That date already exists. Input another date.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            else
                return false;
        }

        public bool deleteTip(String date)
        {
            string query="DELETE FROM tip WHERE dateTip = \'"+date+"\';";
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Selected tip cannot be deleted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            return false;
        }

    }
}
